import java.awt.*;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.awt.event.KeyEvent;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;

import javax.swing.*;

public class project3 extends JFrame
{
	
	// Connection
	static String login = "zhu173";
	static String password = "cs408";
	static String connectionURL = "jdbc:mysql://mydb.ics.purdue.edu:3306/zhu173";
	static Connection conn = null;
	static Statement stmt = null;
	static ResultSet rs = null;

	// global variables
	static int currentClassCounter = 0;
	static int currentEvaluationCounter = 0;
	// button
	private int facultyButtonClicked = -1;
	private int studentButtonClicked = -1;

	public project3()
	{
		
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			System.out.println("Where is your MySQL JDBC Driver?");
			e.printStackTrace();
			return;
		}
		
		try
		{
			conn = DriverManager.getConnection(connectionURL, login, password);
			stmt = conn.createStatement();
			System.out.println("Connected database successfully...");
		} catch (Exception e)
		{
			System.out.println("Database connection errors");
		}

		String selectQuery = "select cid from zhu173.CLASS order by cid desc";
		try
		{
			rs = stmt.executeQuery(selectQuery);
			if(rs.next()){
				currentClassCounter = rs.getInt("cid");
				System.out.println("currentClassCounter is  " + currentClassCounter);
			}			
		} catch (Exception e)
		{
			System.out.println("Database currentClassCounter errors: " + e.toString());
		}

		selectQuery = "select eid from zhu173.EVALUATION order by eid desc";
		try
		{
			rs = stmt.executeQuery(selectQuery);
			if (rs.next())
			{
				currentEvaluationCounter = rs.getInt("eid");
				System.out.println("currentEvaluationCounter is " + currentEvaluationCounter);
			}
		} catch (Exception e)
		{
			System.out.println("Database currentEvaluationCounter errors");
		}
	}

	public ArrayList getClassName(String curr_id)
	{
		String selectQuery = "select cid, cname from zhu173.CLASS where fid = "
				+ curr_id;
		ArrayList<String> al = new ArrayList<String>();

		try
		{
			rs = stmt.executeQuery(selectQuery);
			while (rs.next())
			{
				String classId = Integer.toString(rs.getInt("cid"));
				String className = rs.getString("cname");
				al.add(classId);
				al.add(className);
			}
		} catch (Exception e)
		{
			System.out.println("Database get class name errors");
		}
		return al;
	}

	public ArrayList getEvaluationCidAndCname(String curr_id)
	{
		String selectQuery = "select cid from zhu173.EVALUATION where fid = "
				+ curr_id;
		// System.out.println("fid :" + curr_id + " query: " + selectQuery) ;
		ArrayList<String> al = new ArrayList<String>();
		String tempPrevious = null;
		try
		{
			rs = stmt.executeQuery(selectQuery);
			while (rs.next())
			{
				String classId = Integer.toString(rs.getInt("cid"));
				// if(!tempPrevious.equals(classId)){
				al.add(classId);
				// }
				// tempPrevious = classId;
			}
		} catch (Exception e)
		{
			System.out.println("Database get class name errors");
		}
		return al;
	}

	public ArrayList getEvaluationEidAndSnum(String curr_id, String classID)
	{
		String selectQuery = "select eid, snum ,cname, fid from zhu173.EVALUATION where fid = "
				+ curr_id + " and cid = " + classID;
		ArrayList<String> al = new ArrayList<String>();

		try
		{
			rs = stmt.executeQuery(selectQuery);
			while (rs.next())
			{
				String eid = Integer.toString(rs.getInt("eid"));
				String sid = Integer.toString(rs.getInt("snum"));
				String cname = rs.getString("cname");
				String fid = Integer.toString(rs.getInt("fid"));
				al.add(cname);
				al.add(fid);
				al.add(sid);
				al.add(eid);
			}
		} catch (Exception e)
		{
			System.out.println("Database get evaluation eid and sid errors");
		}
		return al;
	}

	public int getNumOfClassFaculty(String curr_id)
	{
		String selectQuery = "select fid, count(*) as num from zhu173.CLASS where fid = "
				+ curr_id + " group by fid";
		int countNumOfClassFacultyCreated = 0;
		try
		{
			rs = stmt.executeQuery(selectQuery);
			if (rs.next())
			{
				countNumOfClassFacultyCreated = rs.getInt("num");
			}
		} catch (Exception e)
		{
			System.out.println("Database get number of class errors");
		}

		return countNumOfClassFacultyCreated;
	}

	public int registerStudent(String classNameField, String studentID, String classIdField)
	{

		String selectQuery = "insert into zhu173.ENROLLED values (" + studentID
				+ ", '" + classNameField + "',"+classIdField+")";
		System.out.println(selectQuery);
		try
		{
			stmt.executeUpdate(selectQuery);
			System.out.println("Successfully registered a student!!!");
			return 1;
		} catch (Exception e)
		{
			System.out.println("Database register student errors");
			return -1;
		}

	}

	public int checkDate(Date da, String tempCname, String tempFid,
			String tempSID, String tempEID, String classID)
	{
		DateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd");
		String selectQuery = "select deadline from zhu173.EVALUATION where eid ="
				+ tempEID + " and fid=" + tempFid + " and snum=" + tempSID
				+ " and cname = '" + tempCname + "'";
		String currentDate = dateFormat.format(da);
		System.out.println(currentDate);
		String sqlDate;
		Date tempSqlDate = null;
		try
		{
			rs = stmt.executeQuery(selectQuery);
			while (rs.next())
			{
				tempSqlDate = rs.getDate("deadline");
			}
		} catch (Exception e)
		{
			System.out.println("Database get deadline errors");
		}
		sqlDate = dateFormat.format(tempSqlDate).toString();
		System.out.println("sql date:" + sqlDate);
		if ((dateFormat.format(tempSqlDate)).toString().compareTo(currentDate) > 0)
		{
			return 1;
		}
		return -1;
	}

	public ArrayList getStudentClass(String sid)
	{
		ArrayList<String> al = new ArrayList<String>();
		String selectQuery = "select zhu173.CLASS.cname as clname, semester, year, meets_at, room from zhu173.CLASS right join zhu173.ENROLLED on zhu173.CLASS.cname = zhu173.ENROLLED.cname where snum ="
				+ sid;
		System.out.println(selectQuery);
		try
		{
			rs = stmt.executeQuery(selectQuery);
			while (rs.next())
			{
				al.add(rs.getString("clname"));
				al.add(rs.getString("semester"));
				al.add(Integer.toString(rs.getInt("year")));
				al.add(rs.getString("meets_at"));
				al.add(rs.getString("room"));
			}
		} catch (Exception e)
		{
			System.out.println("Database get student report of class errors");
		}
		return al;
	}

	public ArrayList getStudentGrades(String sid)
	{
		ArrayList<String> al = new ArrayList<String>();
		String selectQuery = "select eid, cname, evaluationType, grades from zhu173.EVALUATION where snum = "
				+ sid;
		try
		{
			rs = stmt.executeQuery(selectQuery);
			while (rs.next())
			{
				al.add(Integer.toString(rs.getInt("eid")));
				al.add(rs.getString("cname"));
				al.add(rs.getString("evaluationType"));
				al.add(rs.getString("grades"));
			}
		} catch (Exception e)
		{
			System.out
					.println("Database get student grade of evaluation errors");
		}

		return al;
	}

	public ArrayList getCalenderOfClasses(String sid)
	{
		DateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd");
		ArrayList<String> al = new ArrayList<String>();
		String selectQuery = "select evaluationType, cname, deadline from zhu173.EVALUATION where snum ="
				+ sid;
		try
		{
			rs = stmt.executeQuery(selectQuery);
			while (rs.next())
			{
				al.add(rs.getString("cname"));
				al.add(rs.getString("evaluationType"));
				al.add((dateFormat.format(rs.getDate("deadline"))).toString());
			}
		} catch (Exception e)
		{
			System.out.println("Database get calender of class errors");
		}

		return al;
	}

	public ArrayList getReportStudentAndGrade(String fid)
	{
		// select class.cname, evaluationType, snum, grades, weight from
		// evaluation inner join class on class.cname = evaluation.cname where
		// class.fid = 101;
		ArrayList<String> al = new ArrayList<String>();
		ArrayList<String> bl = new ArrayList<String>();
		String selectQuery = "select class.cname as cc,evaluationType, deadLine , grades, snum, weight from evaluation inner join class on class.cname = evaluation.cname where class.fid= "
				+ fid + " order by snum asc";
		//
		selectQuery = "SELECT zhu173.EVALUATION.cname, zhu173.EVALUATION.evaluationType,  zhu173.EVALUATION.snum,zhu173.EVALUATION.grades, zhu173.EVALUATION.weight FROM zhu173.EVALUATION WHERE zhu173.EVALUATION.fid="+fid;
		//
		
		//String selectQuery2 = "select distinct sname, cc, semester, year from (select cc, sname,semester, year from student inner join (select class.cname as cc, semester, year, snum as tempNum from evaluation inner join class on class.cname = evaluation.cname where class.fid= "
		//		+ fid
		//		+ " order by snum asc) on snum = tempNum order by snum asc)";
		
		int sid, oldSnumber;
		int count = 0;
		int tempSnumber = -1;
		int studentNum;
		String tempCname = "init";
		String className;
		int y = 0;
		/*
		try
		{
			rs = stmt.executeQuery(selectQuery);
			while (rs.next())
			{
				className = rs.getString("cc");
				studentNum = rs.getInt("snum");
				if ((tempSnumber == studentNum && tempCname.equals(className))
						|| tempSnumber == -1 || className.equals("init"))
				{
					int tempGrade = rs.getInt("grades");
					int tempWeight = rs.getInt("weight");
					count = count + tempGrade * tempWeight;
					System.out.println("snum is: " + studentNum);
				} else
				{
					al.add(Integer.toString(count / 100));
					count = 0;
					int tempGrade = rs.getInt("grades");
					int tempWeight = rs.getInt("weight");
					count = count + tempGrade * tempWeight;
				}
				tempSnumber = studentNum;
				tempCname = className;
			}
			al.add(Integer.toString(count / 100));
		} catch (Exception e)
		{
			System.out
					.println("Database get student grade of evaluation errors");
		}
		*/
		int x = 0;
		
		
		try
		{
			rs = stmt.executeQuery(selectQuery);
			while (rs.next())
			{
			String temp=null;
				temp=rs.getString("cname");
				if(temp.equals(null)){
					temp= "none";
				}
				bl.add(temp);

				temp=rs.getString("snum");
				if(temp.equals(null)){
					temp= "none";
				}
				bl.add(temp);

				
				temp=rs.getString("weight");
				if(temp==null){
					temp= "none";
				}
				bl.add(temp);
				
				
				temp=rs.getString("evaluationType");
				if(temp.equals(null)){
					temp= "none";
				}
				
				bl.add(temp);
				
				
				temp=Integer.toString(rs.getInt("grades"));
				if(temp.equals(null)){
					temp= "none";
				}
				bl.add(temp);
				x++;

			}
		} catch (Exception e)
		{
			System.out.println("Database get report of class errors");
		}
		
		for (int i = 0; i < bl.size(); i++)
		{
			System.out.println("bl " + bl.get(i));
		}
		return bl;
	}
	
	public ArrayList reportOfClass(String fid)
	{
		ArrayList<String> al = new ArrayList<String>();
		/*String selectQuery = "select clname, meets_at, room, numStudent, numEvaluation from (select cname, meets_at, room from class where fid ="
				+ fid
				+ ") inner join (select clname, count(num) as numStudent, count(eid) as numEvaluation from (select clname, num, eid from (select cname as clname, count(snum) as num from (select class.cname, snum from class inner join enrolled on class.cname = enrolled.cname where fid ="
				+ fid
				+ ") group by cname) inner join evaluation on clname = cname) group by clname) on clname = cname";*/
		
		
		 String selectQuery= "SELECT zhu173.ENROLLED.cid, zhu173.CLASS.room, zhu173.CLASS.meets_at, zhu173.CLASS.cname as clname, COUNT( DISTINCT zhu173.ENROLLED.snum) as numStudent, IFNULL(COUNT( DISTINCT zhu173.EVALUATION.eid),0) as numEvaluation FROM zhu173.CLASS LEFT JOIN  zhu173.EVALUATION ON (zhu173.EVALUATION.cid=zhu173.CLASS.cid), zhu173.FACULTY, zhu173.ENROLLED WHERE zhu173.CLASS.cid=zhu173.ENROLLED.cid  AND zhu173.CLASS.fid="+fid+ " AND zhu173.FACULTY.fid=zhu173.CLASS.fid GROUP BY zhu173.ENROLLED.cid";
	
		System.out.println("report of class: " + selectQuery);
		try
		{
			rs = stmt.executeQuery(selectQuery);
			while (rs.next())
			{
				al.add(rs.getString("clname"));
				al.add(rs.getString("meets_at"));
				al.add(rs.getString("room"));
				al.add(rs.getString("numStudent"));
				al.add(rs.getString("numEvaluation"));
			}
		} catch (Exception e)
		{
			System.out.println("Database get report of class errors");
		}
		return al;
	}

	public int modifyEvaluation(String tempEID, String evaluationTypeField,
			String evaluationWeightField, String evaluationDeadlineField,
			String evaluationGradeField, String tempCname, String stringCID,
			String tempFid, String tempSID)
	{
		DateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd");
		String selectQuery = "select deadline from evaluation where eid ="
				+ tempEID + " and fid=" + tempFid + " and snum=" + tempSID
				+ " and cname = '" + tempCname + "'";
		String selectQuery2 = "update zhu173.EVALUATION set evaluationType='"
				+ evaluationTypeField + "', weight=" + evaluationWeightField
				+ ", deadLine=str_to_date('" + evaluationDeadlineField
				+ "','%d,%m,%Y')," + "grades=" + evaluationGradeField
				+ " where eid=" + tempEID;
		System.out.println("query: " + selectQuery2);


		try
		{
			stmt.executeUpdate(selectQuery2);
			System.out.println("Successfully modified this evaluation!");
			return 1;
				
		} catch (Exception e)
		{
			System.out.println("Database modify class errors");
			return -1;
		}
		
	}

	public int createEvaluation(String evaluationStudentIDField,
			String evaluationTypeField, String evaluationWeightField,
			String evaluationDeadlineField, String evaluationGradeField,
			String evaluationClassNameField, String evaluationClassNum,
			String evaluationFid, String studentID)
	{
		currentEvaluationCounter++;
		System.out.println("it is " + currentEvaluationCounter);
		String selectQuery = "insert into zhu173.EVALUATION values ("
				+ currentEvaluationCounter + ",'" + evaluationTypeField + "',"
				+ evaluationWeightField + ", str_to_date('"
				+ evaluationDeadlineField + "','%d,%m,%Y'),"
				+ evaluationGradeField + ",'" + evaluationClassNameField + "',"
				+ evaluationClassNum + "," + evaluationFid + "," + studentID
				+ ")";
		
		System.out.println("it is: " + selectQuery);
		try
		{
			stmt.executeUpdate(selectQuery);
			System.out.println("Successfully!");
			return 1;
		} catch (Exception e)
		{
			System.out.println("Database createEvaluation errors" + e.toString());
			return -1;
		}
	}

	public int modifyClass(String classIDField, String classNameField,
			String semesterField, String yearField, String meetsAtField,
			String roomField, String curr_id)
	{
		System.out.println(classIDField + " " + classNameField + " "
				+ semesterField + " " + yearField + " " + meetsAtField + " "
				+ roomField + " " + curr_id);
		String selectQuery = "update class set cname='" + classNameField
				+ "',semester='" + semesterField + "',year=" + yearField
				+ ", meets_at= to_date('" + meetsAtField + "','HH:MI'),"
				+ "room='" + roomField + "' where fid = " + curr_id
				+ " and cid =" + classIDField;

		System.out.println("update query is: " + selectQuery);
		try
		{
			stmt.executeUpdate(selectQuery);
			System.out.println("Successfully modified a class!!!");
			return 1;
		} catch (Exception e)
		{
			System.out.println("Database modify class errors");
			return -1;
		}
	}

	public int createClass(String classNameField, String semesterField,
			String yearField, String meetsAtField, String roomField,
			String curr_id)
	{
		currentClassCounter++;
		String selectQuery = "insert into zhu173.CLASS values (" + currentClassCounter
				+ ",'" + classNameField + "','" + semesterField + "',"
				+ yearField + ", str_to_date('" + meetsAtField + "','%d,%m'),'"
				+ roomField + "'," + curr_id + ")";
		/*String selectQuery = "insert into zhu173.CLASS values (" + currentClassCounter
				+ ",'" + classNameField + "','" + semesterField + "',"
				+ yearField + ", to_date('" + meetsAtField + "','HH:MI'),'"
				+ roomField + "'," + curr_id + ")";*/
		System.out.println("it is: "+ selectQuery);
		try
		{
			stmt.executeUpdate(selectQuery);
			System.out.println("Successfully created a class!");
			return 1;
		} catch (Exception e)
		{
			System.out.println("Database createClass errors");
			return -1;
		}
	}

	public String getUserName(String userNumber)
	{
		String tempName = null;
		String selectQuery = "select sname from zhu173.STUDENT where snum = "
				+ Integer.parseInt(userNumber);
		try
		{
			rs = stmt.executeQuery(selectQuery);
			while (rs.next())
			{
				tempName = rs.getString("sname");
			}
		} catch (Exception e)
		{
			System.out.println("Database verify student errors");
		}
		return tempName;
	}

	public String getFacultyName(String facultyNumber)
	{
		String tempName = null;
		String selectQuery = "select fname from zhu173.FACULTY where fid = "
				+ Integer.parseInt(facultyNumber);
		try
		{
			rs = stmt.executeQuery(selectQuery);
			while (rs.next())
			{
				tempName = rs.getString("fname");
			}
		} catch (Exception e)
		{
			System.out.println("Database verify student errors");
		}
		return tempName;

	}

	public int verifyFaculty(String facultyNumber, String facultyPassword)
	{
		String selectQuery = "select fid, fpassword from zhu173.FACULTY";
		try
		{
			rs = stmt.executeQuery(selectQuery);
			while (rs.next())
			{
				int id = rs.getInt("fid");
				String tempid = Integer.toString(id);
				String tempPassword = rs.getString("fpassword");
				if (facultyNumber.equals(tempid)
						&& facultyPassword.equals(tempPassword))
				{
					return 1;// correct
				}
			}
		} catch (Exception e)
		{
			System.out.println("Database verify faculty errors");
		}
		return -1;// incorrect

	}

	public int verifyStudent(String studentNumber, String studentPassword)
	{
		String selectQuery = "select snum, spassword from zhu173.STUDENT";
		int temp = -1;
		try
		{
			rs = stmt.executeQuery(selectQuery);
			while (rs.next())
			{
				int id = rs.getInt("snum");
				String tempid = Integer.toString(id);
				String tempPassword = rs.getString("spassword");
				System.out.println(tempid + " " + tempPassword);
				if (studentNumber.equals(tempid)
						&& studentPassword.equals(tempPassword))
				{
					System.out.println(111);
					return 1;// correct

				}
			}
		} catch (Exception e)
		{
			System.out.println("Database verify student errors");
		}
		return -1;// incorrect
	}

	public int verifyStudentID(String studentNumber)
	{
		String selectQuery = "select snum from zhu173.STUDENT";
		try
		{
			if (!studentNumber.equals(null))
			{
				rs = stmt.executeQuery(selectQuery);
				while (rs.next())
				{
					int id = rs.getInt("snum");
					if (id == Integer.parseInt(studentNumber))
					{
						return 1;
					}
				}
			}
		} catch (Exception e)
		{
			System.out.println("Database verify student ID errors");
		}
		return -1;// incorrect
	}

	public void myGrades()
	{

	}
	
	public int checkDate(int day, int month, int year){
		int leap = 0;
		//Leap year
		if(year%4 == 0)
			leap = 1;
		//Check even months
		if((month == 4)||(month == 6)||(month == 9)||(month == 11)){
			if(day <= 30)
				return 1;
		}else if(month == 2){
			if(leap == 0){
				if(day <= 28)
					return 1;
			}else{
				if(day <= 29)
					return 1;
			}
		}
		//No need to check odd months because options provided do not exceed its proper value.
		
		return 0;
	}

	public void myClasses()
	{
		String selectQuery = "select snum, class.cname, meets_at, room from class inner join enrolled on class.cname = enrolled.cname";
		try
		{
			rs = stmt.executeQuery(selectQuery);
			while (rs.next())
			{
				int id = rs.getInt("snum");
				// if(id == ???)
				System.out.println("id is : " + id);
			}
		} catch (Exception e)
		{
			System.out.println("Database test2 errors");
		}
	}
	
	public int closeCon(){
		try
		{
			conn.close();
			stmt.close();
			System.out.println("System successfully exit!");
			return 1;
		} catch (Exception e)
		{
			System.out.println("Database test2 errors");
			return -1;
		}
		
	}
	public static void main(String[] args)
	{
		project3 test = new project3();
	}
}
